package daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dao.BackCollegeDao;
import pojo.College;
import utils.DruidUtils;


public class BackCollegeDaoImpl implements BackCollegeDao{

	@Override
	public ArrayList<College> getCollege() {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from college";
		ArrayList<College> cList = new ArrayList<College>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				College c = new College();
				c.setCollegeid(res.getInt("collegeid"));
				c.setCollegeName(res.getString("collegeName"));
				c.setCollegeProvince(res.getInt("collegeProvince"));
				c.setCollegeLogo(res.getString("collegeLogo"));
				cList.add(c);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cList;
	}

	@Override
	public int addCollege(int cProvince, String cName, String cLogo) {
		Connection conn = DruidUtils.getConnection();
		String sql = "insert into college values(0,?,?,?)";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setString(1, cName);
			pre.setInt(2, cProvince);
			pre.setString(3, cLogo);
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int modifyCollege(int collegeid, String collegeName, int collegeProvince, String collegeLogo) {
		Connection conn = DruidUtils.getConnection();
		String sql = "update college set collegeName=?, collegeProvince=?, collegeLogo= ? where collegeid=?;";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setString(1, collegeName);
			pre.setInt(2, collegeProvince);
			pre.setString(3, collegeLogo);
			pre.setInt(4, collegeid);
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int delCollege(int collegeid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "delete from college where collegeid = ?";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setInt(1, collegeid);
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

}
